﻿/*== TSL_prc_PerfCounterValuesByInstanceId ===============================================================
* Gets all of the samples from a given test run for the given prefmon instance for
* use in building a chart
* EXAMPLE:
EXEC	TSL_prc_PerfCounterValuesByInstanceId
		@RunId = 9,
		@InstanceId = 179
===============================================================================*/
	--START CODE--
	CREATE PROCEDURE TSL_prc_PerfCounterValuesByInstanceId
		@RunId  INT
		,@InstanceId INT
	AS

		DECLARE @offset DateTime
		SET @offset = (SELECT TOP 1 IntervalStartTime FROM LoadTestRunInterval WHERE LoadTestRunId = @RunId ORDER BY IntervalStartTime ASC)

		SELECT a.ComputedValue, DATEDIFF(second, @offset, b.IntervalStartTime) AS [Test Time] FROM LoadTestPerformanceCounterSample AS a
		INNER JOIN LoadTestRunInterval AS b ON a.LoadTestRunId = b.LoadTestRunId AND a.TestRunIntervalId = b.TestRunIntervalId
		WHERE a.LoadTestRunId = @RunId AND a.InstanceId = @InstanceId
		ORDER BY [Test Time] ASC
	GO
	--START CODE--
	GRANT EXECUTE ON TSL_prc_PerfCounterValuesByInstanceId TO PUBLIC
	GO


/*== TSL_prc_PerfCounterValuesFromPageSamples ============================================================
* Gets all of the samples from a given test run for the given prefmon instance for
* use in building a chart
* EXAMPLE:
EXEC	TSL_prc_PerfCounterValuesFromPageSamples
		@RunId = 9,
		@RequestUri = 'SomePageName.aspx'
===============================================================================*/
	--START CODE--
	CREATE PROCEDURE TSL_prc_PerfCounterValuesFromPageSamples
	@RunId  INT
	,@RequestUri nvarchar(2048)
	,@RequestId nvarchar(5)
	AS

		DECLARE @offset BIGINT
			SELECT @offset = MIN(SampleTimeStamp) FROM LoadTestPerformanceCounterSample WHERE LoadTestRunId = @RunId AND TestRunIntervalId = 0

		DECLARE @InstanceId INT
			SELECT @InstanceId = InstanceId
			  FROM MTSL_View_PerfmonInstanceNamesAndIds
			  WHERE LoadTestRunId = @RunId
			  AND CounterName = 'Avg. Page Time'
			  AND [LoadTestItemId] = @RequestId
   
		SELECT 
			ComputedValue
			, CAST((SampleTimeStamp - @offset) / 10000000 AS INT) AS [Page Time]  
			FROM LoadTestPerformanceCounterSample WHERE LoadTestRunId = @RunId 
			AND InstanceId = @InstanceId 
			ORDER BY [Page Time] ASC
	GO
	--START CODE--
	GRANT EXECUTE ON TSL_prc_PerfCounterValuesFromPageSamples TO PUBLIC
	GO


/*== TSL_prc_PerfCounterValuesFromTransactionSamples =====================================================
* Gets all of the samples from a given test run for the given prefmon instance for
* use in building a chart
* EXAMPLE:
EXEC	TSL_prc_PerfCounterValuesFromTransactionSamples
		@RunId = 9,
		@TransactionName = 'Some Transaction Name'
===============================================================================*/
	--START CODE--
	CREATE PROCEDURE [dbo].[TSL_prc_PerfCounterValuesFromTransactionSamples]
		@RunId  INT
		,@TransactionName nvarchar(2048)
	AS

		DECLARE @offset BIGINT
			SELECT @offset = MIN(SampleTimeStamp) FROM LoadTestPerformanceCounterSample WHERE LoadTestRunId = @RunId AND TestRunIntervalId = 0

		DECLARE @InstanceId INT
			SELECT @InstanceId = InstanceId
			  FROM MTSL_View_PerfmonTransactionNamesAndIDs
			  WHERE LoadTestRunId = @RunId
			  AND CounterName = 'Avg. Transaction Time'
			  AND TransInstanceName = @TransactionName
   
		SELECT 
			ComputedValue
			, CAST((SampleTimeStamp - @offset) / 10000000 AS INT) AS [Test Time]  
			FROM LoadTestPerformanceCounterSample WHERE LoadTestRunId = @RunId 
			AND InstanceId = @InstanceId 
			ORDER BY [Test Time] ASC

	GO
	--START CODE--
	GRANT EXECUTE ON TSL_prc_PerfCounterValuesFromTransactionSamples TO PUBLIC
	GO


/*== TSL_prc_PerfCounterValuesFromTestCaseSamples ========================================================
* Gets all of the samples from a given test run for the given prefmon instance for
* use in building a chart
* EXAMPLE:
EXEC	TSL_prc_PerfCounterValuesFromTestCaseSamples
		@RunId = 9,
		@TestCaseName = 'Some Test Name'
===============================================================================*/
	--START CODE--
	CREATE PROCEDURE TSL_prc_PerfCounterValuesFromTestCaseSamples
		@RunId  INT
		,@TestCaseName nvarchar(2048)
	AS

		DECLARE @offset BIGINT
			SELECT @offset = MIN(SampleTimeStamp) FROM LoadTestPerformanceCounterSample WHERE LoadTestRunId = @RunId AND TestRunIntervalId = 0

		DECLARE @InstanceId INT
			SELECT @InstanceId = InstanceId
			  FROM MTSL_View_PerfmonInstanceNamesAndIds
			  WHERE LoadTestRunId = @RunId
			  AND CounterName = 'Avg. Test Time'
			  AND Instance = @TestCaseName
  
		SELECT 
			ComputedValue
			, CAST((SampleTimeStamp - @offset) / 10000000 AS INT) AS [Test Time]  
			FROM LoadTestPerformanceCounterSample WHERE LoadTestRunId = @RunId 
			AND InstanceId = @InstanceId 
			ORDER BY [Test Time] ASC
	GO
	--START CODE--
	GRANT EXECUTE ON TSL_prc_PerfCounterValuesFromTestCaseSamples TO PUBLIC
	GO

	